package com.example.demo.account.repository;

import com.example.demo.account.entity.SysUser;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 描述：
 *
 * @author littlecar
 * @date 2019/9/5 10:23
 */
@Repository
public interface UserRepository extends JpaRepository<SysUser,Long> {
    /**
     * 根据username查找SysUser
     * @param username
     * @return SysUser
     */
    SysUser findByUsername(String username);

    List<SysUser> findByUsernameLike(String username);
    @Query(nativeQuery = true, value = " select s.* from user s where s.name like ?1 order by s.name")
    List<SysUser> findByNameLike(String name);

    @Query(nativeQuery = true, value = " select s.* from user s where s.name like ?1 order by s.name limit ?2,?3")
    List<SysUser> findPageByNameLike(String name,Integer page,Integer limit);

    SysUser findByName(String name);

    List<SysUser> findBySfz(String sfz);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where u.role_id=4) order by s.name")
    List<SysUser> findAllSchoolAdmin();

    @Query(nativeQuery = true, value = " select s.* from user s  order by s.name limit ?1,?2 ")
    List<SysUser> findPageAll(Integer pages, Integer limit);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where u.role_id=4) " +
            " and s.name like ?1 order by s.name limit ?2,?3 ")
    List<SysUser> findPageSchoolAdminByNameLike(String admin_name, int pages, int limit);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where u.role_id=4) " +
            " and s.name like ?1 order by s.name  ")
    List<SysUser> findSchoolAdminByNameLike(String admin_name);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where u.role_id=4) order by s.name limit ?1,?2")
    List<SysUser> findPageAllSchoolAdmin(int pages, int limit);

    @Query(nativeQuery=true, value = "select s.* from user s where s.id in(select u.user_id from user_role u where u.school_id=?1 and u.role_id=?2)")
    List<SysUser> findUserBySchoolIdAndRoleId(Long school_id, Long role_id);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where (u.role_id =4 and u.school_id=?2) or (u.role_id=6)) " +
            " and s.name like ?1 order by s.name limit ?3,?4 ")
    List<SysUser> findPageBzrByNameLike(String bzr_name, Long xxxx_id, int pages, int limit);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where (u.role_id =4 and u.school_id=?2) or (u.role_id=6)) " +
            " and s.name like ?1 order by s.name ")
    List<SysUser> findBzrByNameLike(String bzr_name, Long xxxx_id);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where (u.role_id =4 and u.school_id=?1) or (u.role_id=6)) order by s.name limit ?2,?3")
    List<SysUser> findPageAllBzr(Long xxxx_id, int pages, int limit);

    @Query(nativeQuery = true, value = " select s.* from user s where s.id in(select distinct u.user_id from user_role u where (u.role_id =4 and u.school_id=?1) or (u.role_id=6)) order by s.name")
    List<SysUser> findAllBzr(Long xxxx_id);
}
